13. Quiz: WITH
WITH Quizzes
Essentially a WITH statement performs the same task as a Subquery. Therefore, you can write any of the queries we worked with in the "Subquery Mania" using a WITH. That's what you'll do here. Try to perform each of the earlier queries again, but using a WITH instead of a subquery.
Workspace
This section contains either a workspace (it can be a Jupyter Notebook workspace or an online code editor work space, etc.) and it cannot be automatically downloaded to be generated here. Please access the classroom with your account and manually download the workspace to your local machine. Note that for some courses, Udacity upload the workspace files onto https://github.com/udacity, so you may be able to download them there.
Workspace Information:
- Default file path:
- Workspace type: sql-evaluator
- Opened files (when workspace is loaded): n/a
Above is the ERD for the database again - it might come in handy as you tackle the quizzes below. You should write your solution as using a WITH statement, not by finding one solution and copying the output. The importance of this is that it allows your query to be dynamic in answering the question - even if the data changes, you still arrive at the right answer.
Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.
For the region with the largest sales total_amt_usd, how many total orders were placed?
How many accounts had more total purchases than the account name which has bought the most standard_qty paper throughout their lifetime as a customer?
For the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?
What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?
What is the lifetime average amount spent in terms of total_amt_usd, including only the companies that spent more per order, on average, than the average of all orders.